package org.ccay.excel.exporter.service.impl.internal;

import java.io.BufferedOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.vfs2.FileObject;
import org.apache.commons.vfs2.FileSystemException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.ccay.core.exception.ApplicationException;
import org.ccay.core.io.FileStoreType;
import org.ccay.core.log.CcayLoggerFactory;
import org.ccay.core.log.ILogger;
import org.ccay.core.util.CollectionUtil;
import org.ccay.core.util.ConverterUtil;
import org.ccay.core.util.StreamUtil;
import org.ccay.core.util.StringUtil;
import org.ccay.excel.exception.ExcelExportConstants;
import org.ccay.excel.exception.ExcelExportException;
import org.ccay.excel.exporter.IFieldProcessor;
import org.ccay.excel.meta.SheetMeta;
import org.ccay.excel.util.impl.ExcelUtil;
import org.ccay.registry.util.RegistryQueryUtil;
import org.ccay.vfs.ApacheVFSFileObject;
import org.ccay.vfs.VFS;
import org.ccay.vfs.VFSException;

/**
 * 把提供的数据写入到excel 并输出文档
 * 
 * @author chaowangbang
 * 执行步骤
 * 1.  new PoiStreamWriter 
 * 2.  creatSheet
 * 
 */
public class PoiStreamWriter {
	
	private static final ILogger log = CcayLoggerFactory.getLogger(ExcelStreamWriter.class);
	
	private static int ExportColumnWidth=500;
	private static int ExportRowHeight=300;
	// 输出的文件
	private String fileName;
	// excel对象
	private SXSSFWorkbook excelWorkbook;
	// 标题列 1 以后所有的都将减1
	private static final int TITLEINDEX = 1;
	
	/**
	 * 输出文件大小
	 */
	private long fileSize = 0;
	
	// 当前sheet
	private Sheet sheet;

	private SheetMeta  sheetMeta;
	// 列对应的数据类型
	private Map<Integer, String> dataType;
	
	//当前处理sheet的title
	private String[] titles;
	//当前处理sheet的title 批注
	private String[] annotations;
	
	//当前处理的行数据 titles value
	private Map<String, Object> rowData;
	
	private CellStyle dateCellStyle = null;
	
	private CellStyle timestampCellStyle = null;
	
	private XSSFDataFormat format = null;
	
	private CellStyle intCellStyle = null;
	
	IFieldProcessor fieldP = null;
	
	private Map<String, Object> userParameters;

	// 创建生成excel的对象
	public PoiStreamWriter(String fileName) {
		this.fileName = fileName;
		excelWorkbook = new SXSSFWorkbook(-1);
		//设置输出到零时文件
		excelWorkbook.setCompressTempFiles(true);
		//日期类型的样式
		dateCellStyle = excelWorkbook.createCellStyle();
		//时间戳类型的样式
		timestampCellStyle  =  excelWorkbook.createCellStyle();
		//int类型的样式
		intCellStyle =  excelWorkbook.createCellStyle();
		format = (XSSFDataFormat) excelWorkbook.createDataFormat();
		dateCellStyle.setDataFormat(format.getFormat("yyyy-MM-dd"));
		timestampCellStyle.setDataFormat(format.getFormat("yyyy-MM-dd HH:mm:ss"));
		intCellStyle.setDataFormat(format.getFormat("###0"));
	}


	public void setUserParameters(Map<String, Object> userParameters) {
		this.userParameters = userParameters;
	}
	
	/**
	 * 创建sheet
	 * @param sheetName sheet 名称
	 * @param titles 列头
	 * Created：2013-8-26
	 * @author chaowangbang
	 */
	@Deprecated
	public void creatSheet(String sheetName,String[] titles,SheetMeta sheetMeta) {
		this.creatSheet(sheetName, titles, null, sheetMeta);
	}
	
	/**
	 * 创建sheet
	 * @param sheetName sheet 名称
	 * @param titles 列头
	 * Created：2013-8-26
	 * @author chaowangbang
	 */
	public void creatSheet(String sheetName,String[] titles,String[] annotations,SheetMeta sheetMeta) {
		this.sheet = ExcelUtil.creatSheet(excelWorkbook,sheetName);
		this.titles = titles;
		this.annotations = annotations;
		this.sheetMeta = sheetMeta;
	}

	/**
	 * 把数据写入到excel的列中
	 * 
	 * @param sheet
	 * @param titleRowIndex
	 */
	public void writeTitleData(int titleRowIndex) {
		CellStyle style;
		style = excelWorkbook.createCellStyle();
		// 设置单元格字体
		Font headerFont = excelWorkbook.createFont();
		headerFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
		headerFont.setFontName("arial,verdana,tahoma");
		style.setFont(headerFont);
		// 设置单元格的样式
		style.setWrapText(true);
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		style.setFillForegroundColor(ExcelUtil.getForegroundColorIndex(IndexedColors.SKY_BLUE));
		
		
		// 创建表头的标题列
		Row row = sheet.createRow(titleRowIndex - TITLEINDEX);
		//冻结首行
		sheet.createFreezePane(0,titleRowIndex,0,titleRowIndex);
		// 设置批注时使用
		Drawing patr = sheet.createDrawingPatriarch(); 
		int columnSize = titles.length;
		
		// 获取各列标题的字符长度
		int[] titleLengthArray = new int[columnSize];
		for (int column = 0; column < columnSize; column++) {
			titleLengthArray[column] = StringUtil.getUTF8Length(titles[column]);
		}
		if(this.sheetMeta.getExportColumnWidth()>0){
			ExportColumnWidth = this.sheetMeta.getExportColumnWidth();
		}else {
			ExportColumnWidth = RegistryQueryUtil.getIntegerValue("Ccay.Excel.ExportColumnWidth");
		}
		 if(this.sheetMeta.getExportRowHeight()>0){
			 ExportRowHeight =  this.sheetMeta.getExportRowHeight();
		 }else {
			 ExportRowHeight =  RegistryQueryUtil.getIntegerValue("Ccay.Excel.ExportRowHeight");
		 }
		
		// 遍历Sheet元数据获得Column元数据
		for (int column = 0; column < columnSize; column++) {
			// 创建第column列的表头数据
			Cell cell = row.createCell(column);
			// 根据title长度调整列宽
			int titleWidth = titleLengthArray[column];//StringUtil.getUTF8Length(title);
			sheet.setColumnWidth((short) column, (short) ExportColumnWidth * titleWidth);
			sheet.setDefaultRowHeight((short) ExportRowHeight);
			// 获取标题名称
			cell.setCellValue(titles[column]);
			// 获得标题列单元格的样式
			cell.setCellStyle(style);
			//如果不设置批注，则不需要写入
			if(annotations==null)continue;
			String annotation = annotations[column];
			
			// 设置批注
			if (!StringUtil.isNullOrEmpty(annotation)) {
				ClientAnchor anchor = new XSSFClientAnchor();
				int annotationLength = annotation.length();
				// 显示批注的宽度所占单元格数目
				int useColCount = getCommentUseColCount(column, annotationLength, titleLengthArray);
				anchor.setCol1(cell.getColumnIndex());
				anchor.setCol2(cell.getColumnIndex() + useColCount);
				anchor.setRow1(cell.getRowIndex());
				anchor.setRow2(cell.getRowIndex() + 1);
				
				Comment comment = patr.createCellComment(anchor);
				comment.setColumn(cell.getColumnIndex());
				comment.setRow(cell.getRowIndex());
				RichTextString commentText = new XSSFRichTextString(annotation);
				comment.setString(commentText);
				// 设置批注
				cell.setCellComment(comment);
			}
		}
	}
	
	/*
	 * 计算显示批注的宽度所占单元格数目
	 */
	private int getCommentUseColCount(int currentColNumber, int commentLength, int[] titleLength){
		int useCount = 0;
		int useLength = 0;
		int colunmSize = titleLength.length;
		if (colunmSize == 0) {
			return 1;
		}
		while(useLength < commentLength){
			useLength = useLength + titleLength[(currentColNumber + useCount) % colunmSize];
			useCount++;
		}
		return useCount;
	}

	/**
	 * 把数据写入到excel的列中
	 * 
	 * @param startRowIndex 数据起始行
	 * @param dataList
	 * @param fieldProcessor 列扩展对象
	 */
	public void writeCellData(int startRowIndex,
			List<List<Object>> dataList,Map<String,IFieldProcessor> fieldProcessor) {
		Row row = null;
		List<Object> cellData = null;
		for (int dataRow = 0; dataRow < dataList.size(); dataRow++) {
			cellData = dataList.get(dataRow);
			if(cellData == null){
				continue;
			}
			rowData = new HashMap<String, Object>();
			row = sheet.createRow(startRowIndex + dataRow - PoiStreamWriter.TITLEINDEX);
			for (int cellnum = 0; cellnum < titles.length; cellnum++) {
				//获取当前列的 导出类型
				String cellType = dataType.get(cellnum);
				Cell cell =  row.createCell(cellnum);
				Object cellValue = cellData.get(cellnum);
				//如果获取到的value是空 那么直接赋值
				if (null == cellValue || StringUtil.isNullOrEmpty(cellValue.toString())) {
					rowData.put(titles[cellnum], "");
					cell.setCellValue("");
					//进行下次赋值
					continue;
				}
				setCellValue(cell,cellValue,cellType);
				rowData.put(titles[cellnum], cellValue);
			}
			executeProcessor(row,rowData,fieldProcessor);
			//每一百行  清除一次缓存对象  lWX211179 2015-07-21
			if(dataRow%100==0){
				flushRows();
			}
		}
		flushRows();
//		try {
//			((SXSSFSheet)sheet).flushRows();
//		} catch (IOException e) {
//			log.error("sheet.flushRows() error",e);
//		}
	}
	/**
	 * 把数据写入到excel的列中
	 * @param startRowIndex 起始行
	 * @param cellSize  列数
	 * @param rs 数据源 
	 * @param maxTotalNum 容许的最大数据
	 * @return 导出的条数
	 * @throws ApplicationException
	 * Created：2013-4-1
	 * @author chaowangbang
	 */
	public int writeCellData(int startRowIndex, String[] fieldNames,
			ResultSet rs,Long maxTotalNum,Map<String,IFieldProcessor> fieldProcessor) throws ApplicationException {
		Row row = null;
		int dataRow =0;
		try {
			while (rs.next()) {
				row = sheet.createRow(startRowIndex + dataRow - PoiStreamWriter.TITLEINDEX);
				rowData = new HashMap<String, Object>();
				for (int cellnum = 0; cellnum < titles.length; cellnum++) {
					//获取当前列的 导出类型
					String cellType = dataType.get(cellnum);
					Cell cell =  row.createCell(cellnum);
					Object valueOf = this.getResultSetValueOf(rs,cellType,fieldNames[cellnum]);
					//如果获取到的value是空 那么直接赋值
					if (null == valueOf || StringUtil.isNullOrEmpty(valueOf.toString())) {
						rowData.put(titles[cellnum], "");
						cell.setCellValue("");
						//进行下次赋值
						continue;
					}
					setCellValue(cell,valueOf,cellType);
					rowData.put(titles[cellnum], valueOf);
				}
				executeProcessor(row,rowData,fieldProcessor);
				dataRow ++;
				this.rowEvent(dataRow,maxTotalNum);
			}
		} catch (SQLException e1) {
			log.error("writeCellData error for ResultSet",e1);
			throw new ExcelExportException("ccay.excel.00110010",e1);
		}
		this.flushRows();
		return dataRow;
	}
	
	/**
	 * 执行当前行的 列扩展 执行自定义操作列
	 * @param row
	 * @param fieldProcessor
	 * Created：2013-8-26
	 * @author chaowangbang
	 */
	private void executeProcessor(Row row,Map<String, Object> rowData,Map<String,IFieldProcessor> fieldProcessor){
		
		if(CollectionUtil.isNullOrEmpty(fieldProcessor)){
			return;
		}
		for (int cellnum = 0; cellnum <titles.length; cellnum++) {
			//执行自定义操作列
			fieldP = fieldProcessor.get(titles[cellnum]);
			if(null!=fieldP){
				fieldP.processor(row.getCell(cellnum),rowData,userParameters);
			}
		}
	}
	/**
	 * 根据导出类型从ResultSet中获取数据
	 * @param rs
	 * @param type
	 * @param fieldName
	 * @return
	 * @throws SQLException
	 */
	private Object getResultSetValueOf(ResultSet rs,String type,String fieldName) throws SQLException{
		String celType = ExcelExportConstants.getExcelExportStupportMap().get(StringUtil.toLower(type));
		if("string".equalsIgnoreCase(celType)){
			return rs.getString(fieldName);
		}
		if("int".equalsIgnoreCase(celType)){
			return rs.getInt(fieldName);
		}
		if("double".equalsIgnoreCase(celType)){
			return rs.getDouble(fieldName);
		}
		if("date".equalsIgnoreCase(celType)){
			return rs.getDate(fieldName);
		}
		if("timestamp".equalsIgnoreCase(celType)){
			return rs.getTimestamp(fieldName);
		}
		if("boolean".equalsIgnoreCase(celType)){
			return rs.getBoolean(fieldName);
		}
		if("byte".equalsIgnoreCase(celType)){
			return rs.getByte(fieldName);
		}
		return rs.getObject(fieldName);
	}
	
	private  void rowEvent(int dataRow,Long maxTotalNum) throws ExcelExportException{
		//每一百行  清除一次缓存对象
		if(dataRow%100==0){
			flushRows();
		}
		if (dataRow > maxTotalNum) {
			throw new ExcelExportException("ccay.excel.00110009", maxTotalNum);
		} 
	}
	
	/**
	 * 刷新sheet
	 * Created：2013-4-1
	 * @author chaowangbang
	 */
	private void flushRows(){
		try {
			((SXSSFSheet)sheet).flushRows();
		} catch (IOException e) {
			log.error("sheet.flushRows() error",e);
		}
	}
	/**
	 * 设置导出数据的格式
	 * @param cell
	 * @param o
	 * @param type
	 */
	public void  setCellValue(Cell cell,Object o,String type){
		String celType = ExcelExportConstants.getExcelExportStupportMap().get(StringUtil.toLower(type));
		if("string".equalsIgnoreCase(celType)){
			cell.setCellValue(String.valueOf(o));
			return ;
		}
		if("int".equalsIgnoreCase(celType)){
			cell.setCellValue(Integer.valueOf(String.valueOf(o)));
			cell.setCellStyle(intCellStyle);
			return ;
		}
		if("double".equalsIgnoreCase(celType)){
			cell.setCellValue(Double.valueOf(String.valueOf(o)));
			return ;
		}
		if("date".equalsIgnoreCase(celType)){
			cell.setCellValue((Date)o);
			cell.setCellStyle(dateCellStyle);
			return ;
		}
		if("timestamp".equalsIgnoreCase(celType)){
			cell.setCellValue((Date)o);
			cell.setCellStyle(timestampCellStyle);
			return ;
		}
		if("boolean".equalsIgnoreCase(celType)){
			//数据强制转化bug
			//cell.setCellValue((Boolean)o);
			cell.setCellValue(ConverterUtil.toBool(o));
			return ;
		}
		if("byte".equalsIgnoreCase(celType)){
			cell.setCellValue(Integer.valueOf(String.valueOf(o)));
			return ;
		}
		cell.setCellValue(String.valueOf(o));	
	}
	/**
	 * 输出excel文件
	 * 
	 * @throws IOException
	 */
	public void excelExportFile() throws ApplicationException {
		//创建归档文件对象
		ApacheVFSFileObject fileObject = new ApacheVFSFileObject("excel",this.fileName,FileStoreType.ARCHIVE);
		OutputStream output = null;
		try {
			output = fileObject.getOutputStream();
			excelWorkbook.write(output);
		} catch (IOException e) {
			throw new ExcelExportException(ExcelExportConstants.EXCEL_EXPORT_FILE_ERROR,e,this.fileName);
		} finally {
			fileSize = fileObject.getSize();
			fileObject.close();
		}
	}
	
	/**
	 * 
	 * 把特殊的字符放在cdada里，主要用于xml文件中
	 * @param str
	 * @return
	 */
	public String convert4XML(String str){ 
		if(StringUtil.isNullOrEmpty(str)){
			return "";	
		}
		String[] strArray = new String[]{"'","\"","<",">","(",")","&"};
 		for (String s : strArray) {
			if (str.contains(s)) {
				str = "<![CDATA[" + str + "]]>";
				break;
			}
		}
 		return str;	 
	}
	
	
	/**
	 * 清除对象
	 */
	public void clearObject() {
		if (null != excelWorkbook) {
			excelWorkbook = null;
		}
		if (null != sheet) {
			sheet = null;
		}
		if (null != dataType) {
			dataType.clear();
		}
	}

	/**
	 * 获取文件大小
	 * 
	 * @return
	 * @throws FileSystemException 
	 */
	public long getFileSize() {
		return fileSize;
	}

	public Map<Integer, String> getDataType() {
		return dataType;
	}

	public void setDataType(Map<Integer, String> dataType) {
		this.dataType = dataType;
	}


	public String[] getTitles() {
		return titles;
	}


	public void setTitles(String[] titles) {
		this.titles = titles;
	}


	public String[] getAnnotations() {
		return annotations;
	}


	public void setAnnotations(String[] annotations) {
		this.annotations = annotations;
	}
	
	
}
